package com.api.gbhg.daiyanlu.Dao;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;

/**
 * 会计角色待推送dao层
 * Mr.dyl
 * 2021.04.08
 */
public class AccountingPushedDao {
    /**
     * 根据workflow_currentoperator表的requestId查找报销流程id
     * @param requestId
     * @return
     */
    public static RecordSetTrans getRequestIdDao(String requestId) throws Exception {
        RecordSetTrans rst=new RecordSetTrans();
        StringBuffer sb=new StringBuffer();
        String sql = "select * from workflow_currentoperator where requestid in ("+requestId+") ";
        sb.append(sql);
        rst.executeQuery(sb.toString());
        return rst;
    }

    /**
     * 根据workflow_base表的formId查找报销流程表名id
     * @param id
     * @return
     */
    public static RecordSetTrans getIdDao(String id) throws Exception {
        RecordSetTrans rst = new RecordSetTrans();
        StringBuffer sb=new StringBuffer();
        String sql = "select * from workflow_base where id ="+id+" and version=2";
        sb.append(sql);
        rst.executeQuery(sb.toString());
        return rst;
    }

    /**
     * 根据workflow_bill表的id查找报销流程表名
     * @param formId
     * @return
     */
    public static RecordSetTrans getFromId(String formId) throws Exception {
        RecordSetTrans rst = new RecordSetTrans();
        StringBuffer sb=new StringBuffer();
        String sql = "select * from workflow_bill where id ="+formId+"";
        sb.append(sql);
        rst.executeQuery(sb.toString());
        return rst;
    }

    /**
     * 查询报销流程表里的数据
     * @param tableName 表名
     * @param nd
     * @return
     */
    public static RecordSetTrans getAccountingDao(String tableName, Integer pageNum, Integer pageSize, int nd, String dw) throws Exception {
        RecordSetTrans rst = new RecordSetTrans();
        StringBuffer sb=new StringBuffer();
        String sql = "select * from "+tableName+" f inner join "+tableName+"_dt1 fd1\n" +
                "on f.id=fd1.mainid where fd1.nd="+nd+"";
        sb.append(sql);
        if(!dw.equals("2")){
            sb.append("and f.jbbm="+dw+"");
        }else {
            sb.append(" ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rst.getDBType())){
            sb.append(" order by f.id offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rst.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rst.executeQuery(sb.toString());
        return rst;
    }


//    public static RecordSetTrans getAccountingDao(String tableName, Integer pageNum, Integer pageSize, int nd, String dw) throws Exception {
//        RecordSetTrans rst = new RecordSetTrans();
//        StringBuffer sb=new StringBuffer();
//        String sql = "select *  from "+tableName+"";
//        sb.append(sql);
//        pageNum=(pageNum-1)*pageSize;
//        if("sqlserver".equals(rst.getDBType())){
//            sb.append(" order by Id offset "+pageNum+" rows fetch next "+pageSize+" rows only");
//        }else if("mysql".equals(rst.getDBType())){
//            sb.append(" LIMIT "+pageNum+","+pageSize+"");
//        }
//        rst.executeQuery(sb.toString());
//        return rst;
//    }

    /**
     * 获取报销流程表总条数
     * @param tableName 表名
     * @return
     */
    public int getAccountingCount(String tableName,int nd, String dw){
        RecordSet rs = new RecordSet();
        String StrSQL ="select count(1)AS num from "+tableName+" f inner join "+tableName+"_dt1 fd1\n" +
                "on f.id=fd1.mainid where fd1.nd="+nd+" ";
        rs.executeQuery(StrSQL);
        rs.next();
        if(!dw.equals("2")){
            rs.executeQuery("and f.jbbm="+dw+"");
        }else {
            rs.executeQuery(" ");
        }
        return rs.getInt("num");
    }

    /**
     * 查询报销流程明细表
     * @param tableDetail
     * @return
     */
    public static RecordSetTrans getAccountingDetail(String tableDetail,Integer pageNum,Integer pageSize, int nd, String dw) throws Exception {
        RecordSetTrans rst = new RecordSetTrans();
        StringBuffer sb=new StringBuffer();
        String sql = "select * from "+tableDetail+"_dt1";
        sb.append(sql);
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rst.getDBType())){
            sb.append(" order by Id offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rst.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rst.executeQuery(sb.toString());
        return rst;
    }
}
